package com.sfang.common.util.export;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: user
 * @Date: 2020/11/5 11:28
 * @Version: 1.0
 */
public class ExcelUtils {
    private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * 导出Excel(07版.xlsx)到指定路径下
     *
     * @param path      路径
     * @param excelName Excel名称
     * @param sheetName sheet页名称
     * @param clazz     Excel要转换的类型
     * @param data      要导出的数据
     */
    public static void export2File(String path, String excelName, String sheetName, Class clazz, List data) {
        String fileName = path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue());
        EasyExcel.write(fileName, clazz).sheet(sheetName).doWrite(data);
    }

    /**
     * 导出Excel(07版.xlsx)到web
     *
     * @param response  响应
     * @param excelName Excel名称
     * @param sheetName sheet页名称
     * @param clazz     Excel要转换的类型
     * @param data      要导出的数据
     * @throws Exception
     */
    public static void export2Web(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        excelName = URLEncoder.encode(excelName, "utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + new String((excelName + ExcelTypeEnum.XLSX.getValue()).getBytes(), "utf-8"));

        EasyExcel.write(response.getOutputStream(), clazz).registerWriteHandler(new CustomCellWriteHandler()).sheet(sheetName).doWrite(data);
    }

    /**
     * 导出Excel(07版.xlsx)到web
     *
     * @param response  响应
     * @param excelName Excel名称
     * @param sheetName sheet页名称
     * @param clazz     Excel要转换的类型
     * @param data      要导出的数据
     * @throws Exception
     */
    public static void export2WebBatch(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        excelName = URLEncoder.encode(excelName, "utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + new String((excelName + ExcelTypeEnum.XLSX.getValue()).getBytes(), "utf-8"));
        ServletOutputStream outputStream = response.getOutputStream();

        //获取总数据量
        Integer count = data.size();
        //如果总数据量多余10万，分页导出
        if (count > 100000) {
            //每页多少个
            int max = 100000;
            //必须放到循环外，否则会刷新流
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            for (int i = 0; i < (count / max) + 1; i++) {
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "合同" + (i + 1)).head(clazz)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                excelWriter.write(data, writeSheet);
            }

            //刷新流
            excelWriter.finish();
        } else {
            EasyExcel.write(outputStream, clazz).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("sheet1").doWrite(data);
        }
    }

    /**
     * 导出Excel(07版.xlsx)到web
     *
     * @param response  响应
     * @param excelName Excel名称
     * @param sheetName sheet页名称
     * @param clazz     Excel要转换的类型
     * @param data      要导出的数据
     * @throws Exception
     */
    public static void export2WebWithTemplate(HttpServletResponse response, String excelName, String templatePath, String sheetName, Class clazz, List data) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        excelName = URLEncoder.encode(excelName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + new String((excelName + ExcelTypeEnum.XLSX.getValue()).getBytes(), "UTF-8"));
        //添加响应头信息
        //设置类型
        EasyExcel.write(response.getOutputStream(), clazz).withTemplate(templatePath).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(sheetName).doWrite(data);
    }

    /**
     * 将指定位置指定名称的Excel导出到web
     *
     * @param response  响应
     * @param path      文件路径
     * @param excelName 文件名称
     * @return
     * @throws UnsupportedEncodingException
     */
    public static String export2Web4File(HttpServletResponse response, String path, String excelName) throws UnsupportedEncodingException {
        File file = new File(path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue()));
        if (!file.exists()) {
            return "文件不存在！";
        }

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        excelName = URLEncoder.encode(excelName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());

        try (
                FileInputStream in = new FileInputStream(file);
                ServletOutputStream out = response.getOutputStream();
        ) {
            IOUtils.copy(in, out);
            return "导出成功！";
        } catch (Exception e) {
            log.error("导出excel文件异常", e);
        }

        return "导出失败！";
    }


    /**
     * 导出excel
     *
     * @param fileName 导出的文件名
     * @param response 响应
     * @param request  请求
     * @param lists    导出的数据
     * @param <T>      导出的实体类泛型
     */
    public static <T> void writerExcel(String fileName, HttpServletResponse response, HttpServletRequest request, List<T> lists, Class<T> clazz) {
        String sheetName = fileName;
        try {
            String userAgent = request.getHeader("User-Agent");

            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                // 针对IE或者以IE为内核的浏览器：
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {
                // 非IE浏览器的处理:
                fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            }
            response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", fileName + ".xlsx"));
            response.setHeader("Cache-Control", "no-cache");
            response.setHeader("Pragma", "no-cache");
            response.setDateHeader("Expires", -1);
        } catch (UnsupportedEncodingException e1) {
            log.error("导出excel未知编码异常", e1);
        }
        try {
            EasyExcel.write(response.getOutputStream(), clazz)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .sheet(sheetName)
                    .doWrite(lists);
        } catch (IOException e) {
            log.error("导出excel文件异常", e);
        }
    }

    /**
     * 导出带模板文件的excel
     *
     * @param fileName     导出文件名
     * @param response     响应
     * @param request      请求
     * @param lists        导出的数据
     * @param <T>          导出的实体类泛型
     * @param clazz        导出的实体类
     * @param templatePath 模版文件路径
     */
    public static <T> void writerExcelByTemplate(String fileName, HttpServletResponse response, HttpServletRequest request, List<T> lists, Class<T> clazz, String templatePath) {

        String sheetName = fileName;
        try {
            String userAgent = request.getHeader("User-Agent");
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {
                fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            }
            response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", fileName + ".xlsx"));
            response.setHeader("Cache-Control", "no-cache");
            response.setHeader("Pragma", "no-cache");
            response.setDateHeader("Expires", -1);
        } catch (UnsupportedEncodingException e1) {
            log.error("导出excel未知编码异常", e1);
        }
        try {
            EasyExcel.write(response.getOutputStream(), clazz)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .withTemplate(templatePath)
                    .sheet(0, sheetName)
                    .doWrite(lists);
        } catch (IOException e) {
            log.error("导出excel文件异常", e);
        }
    }

    /**
     * 导出模板信息（自定义表头）
     *
     * @param fileName 导出文件名
     * @param response 响应
     * @param request  请求
     * @param heads    表头信息
     */
    public static void writerExcelWithHead(String fileName, HttpServletResponse response, HttpServletRequest request, List<String> heads) {
        String sheetName = fileName;
        try {
            String userAgent = request.getHeader("User-Agent");
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {
                fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            }
            response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", fileName + ".xlsx"));
            response.setHeader("Cache-Control", "no-cache");
            response.setHeader("Pragma", "no-cache");
            response.setDateHeader("Expires", -1);
        } catch (UnsupportedEncodingException e1) {
            log.error("导出excel未知编码异常", e1);
        }
        try {
            EasyExcel.write(response.getOutputStream(), null)
                    .head(head(heads))
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .sheet(sheetName)
                    .doWrite(new ArrayList());
        } catch (IOException e) {
            log.error("导出excel文件异常", e);
        }
    }

    /**
     * 获取表头
     *
     * @param strings 表头信息
     * @return 表头
     */
    private static List<List<String>> head(List<String> strings) {
        List<List<String>> list = new ArrayList<>();
        for (String string : strings) {
            List<String> head = new ArrayList<>();
            head.add(string);
            list.add(head);
        }
        return list;
    }
}
